package com.seeyon.apps.commonExt.util;

import com.seeyon.apps.commonExt.domain.Cap4Update;
import com.seeyon.apps.commonExt.domain.Cap4Where;
import com.seeyon.apps.commonExt.domain.PageDataExt;
import com.seeyon.cap4.form.bean.*;
import com.seeyon.cap4.form.exception.FormException;
import com.seeyon.cap4.form.modules.engin.base.formData.CAP4FormDataDAO;
import com.seeyon.cap4.form.modules.engin.base.formData.CAP4FormDataManager;
import com.seeyon.cap4.form.modules.engin.base.formData.CAP4FormDataManagerImpl;
import com.seeyon.cap4.form.util.Enums;
import com.seeyon.cap4.form.util.StringUtils;
import com.seeyon.ctp.common.AppContext;
import com.seeyon.ctp.common.exceptions.BusinessException;
import com.seeyon.ctp.util.FlipInfo;
import com.seeyon.ctp.util.JDBCAgent;
import com.seeyon.ctp.util.Strings;
import com.seeyon.ctp.util.UUIDLong;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.map.MapUtil;

import org.apache.commons.lang3.tuple.Pair;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterUtils;
import org.springframework.jdbc.core.namedparam.ParsedSql;

import java.sql.SQLException;
import java.util.*;


public class CAP4FormDataManagerExt extends CAP4FormDataManagerImpl {

    private static final Logger LOGGER = LoggerFactory.getLogger(CAP4FormDataManagerExt.class);


    /**
     * @param flipInfo
     * @param fields
     * @param where
     * @return
     * @description: 支持范围查询
     * @author: huangjin
     * @time: 2025/3/13 15:29
     */
    public static PageDataExt dataAndCount(FlipInfo flipInfo, FormBean formBean, String[] fields, Cap4Where where, boolean includeSubTable)  {
        try (JDBCAgent jdbc = new JDBCAgent()) {
            String fieldNames = fields != null && fields.length > 0 ? " id ," + StringUtils.arrayToString(fields) : "*";
            StringBuilder sql = new StringBuilder();
            String tableName = formBean.getMasterTableBean().getTableName();
            sql.append("select ").append(fieldNames).append(" from ").append(tableName);
            if (where != null) {
                sql.append(where.build());
            }
            CAP4FormDataDAO cap4FormDataDAO = (CAP4FormDataDAO) AppContext.getBean("cap4FormDataDAO");
            jdbc.findNameByPaging(sql.toString(), where.getParameters(), flipInfo);
            List<Map<String, Object>> dataList = flipInfo.getData();
            List<FormDataMasterBean> list = new ArrayList<>();
            for (int i = 0; i < dataList.size(); ++i) {
                Map<String, Object> rowData = dataList.get(i);
                FormDataMasterBean formDataMasterBean = new FormDataMasterBean(rowData, formBean.getMasterTableBean(), new boolean[0]);
                // 是否填充明细表
                if (includeSubTable) {
                    for (FormTableBean tableBean : formBean.getSubTableBean()) {
                        List<FormDataSubBean> subList = cap4FormDataDAO.selectSubDataByMasterId(formDataMasterBean, tableBean);
                        if (Strings.isEmpty(subList)) {
                            Map<String, Object> dtlRowData = new HashMap();
                            FormDataSubBean formDataSubBean = new FormDataSubBean(dtlRowData, tableBean, formDataMasterBean, new boolean[]{true});
                            formDataSubBean.setDb(true);
                            if (subList == null) {
                                subList = new ArrayList();
                            }
                            subList.add(formDataSubBean);
                        }

                        formDataMasterBean.setSubData(tableBean.getTableName(), subList);
                    }
                }
                list.add(formDataMasterBean);
            }
            PageDataExt pageDataExt = new PageDataExt();
            pageDataExt.setData(list);
            pageDataExt.setTotal(flipInfo.getTotal());
            return pageDataExt;
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
            throw new FormException("查询数据失败");
        }
    }

    public static List<FormDataMasterBean> selectMasterDataList(FlipInfo flipInfo, FormBean formBean, String[]
            fields, Cap4Where where) {
        return dataAndCount(flipInfo, formBean, fields, where, true).getData();
    }

    public static PageDataExt dataAndCount(FlipInfo flipInfo, FormBean formBean, String[]
            fields, Cap4Where where) {
       return dataAndCount(flipInfo, formBean, fields, where, true);
    }





    /**
     * 查询明细数据
     * @param flipInfo 分页
     * @param tableBean 子表formBean对象
     * @param fields 字段
     * @param where 筛选条件
     * @return
     */
    public static List<FormDataSubBean> selectSubDataList(FlipInfo flipInfo, FormTableBean tableBean, String[] fields, Cap4Where where) {
        try (JDBCAgent jdbc = new JDBCAgent()) {
            String fieldNames = fields != null && fields.length > 0 ? " id ," + StringUtils.arrayToString(fields) : "*";
            StringBuilder sql = new StringBuilder();
            String tableName = tableBean.getTableName();
            sql.append("select ").append(fieldNames).append(" from ").append(tableName);

            if (where != null) {
                sql.append(where.build());
            }
            jdbc.findNameByPaging(sql.toString(), where.getParameters(), flipInfo);
            List<Map<String, Object>> dataList = flipInfo.getData();

            List<FormDataSubBean> list = new ArrayList<>();

            for (Map<String, Object> dataMap : dataList) {
                //为满足后续生成FormDataSubBean对象，此字段无实际作用
                dataMap.put(Enums.SubTableField.formmain_id.getKey(), UUIDLong.longUUID());
                FormDataSubBean formDataSubBean = new FormDataSubBean(dataMap, tableBean, null, new boolean[]{true});
                list.add(formDataSubBean);
            }

            return list;
        }catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
            throw new FormException("查询数据失败：" + e.getMessage());
        }
    }

    /**
     * 更新表数据
     * @description:
     * @author: huangjin
     * @time: 2025/4/1 16:52
     * @param tableBean
     * @param cap4Update
     * @param where
     */
    public static void update(FormTableBean tableBean, Cap4Update cap4Update, Cap4Where where) {
        try (JDBCAgent jdbc = new JDBCAgent()) {
            String sql = "update " + tableBean.getTableName() + " set " + cap4Update.build() + where.buildWhere();
            Map.Entry<String, List<Object>> stringListEntry = doPrepareNameQuery(sql, where.getParameters());
            jdbc.execute(stringListEntry.getKey(), stringListEntry.getValue());
        }catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
            throw new FormException("更新数据失败：" + e.getMessage());
        }
    }


    public static void truncate(FormTableBean tableBean) {
        if (null == tableBean) {
            return;
        }
        try (JDBCAgent jdbc = new JDBCAgent()) {
            String sql = "truncate table " + tableBean.getTableName();
            jdbc.execute(sql);
        }catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
            throw new FormException("truncate数据失败：" + e.getMessage());
        }
    }
    
    public static void saveOrUpdateFormData(FormBean formBean, Map<String, Object> formParams, boolean fillNull) throws BusinessException, SQLException {
        CAP4FormDataManager cap4FormDataManager = (CAP4FormDataManager) AppContext.getBean("cap4FormDataManager");
        Map<String, Object> masterParams = new HashMap<>();
        Map<String, Object> subParams = new HashMap<>();
        Long masterId;
        for (Map.Entry<String, Object> entry : formParams.entrySet()) {
            if (entry.getValue() instanceof Collection) {
                FormTableBean tableBean = formBean.getSubTableBean().stream()
                        .filter(item -> item.getDisplay().equals(entry.getKey()))
                        .findFirst()
                        .orElse(null);
                if (null == tableBean) {
                    continue;
                }
                List<Map<String,Object>> dtlDataList = (List<Map<String, Object>>) entry.getValue();
                List<Map<String,Object>> newDtlDataList = new ArrayList<>();
                for (Map<String, Object> dltData : dtlDataList) {
                    Map<String, Object> newDetailDataMap = new HashMap<>();
                    for (Map.Entry<String, Object> detailDataMap : dltData.entrySet()) {
                        FormFieldBean fieldBean = tableBean.getFieldBeanByDisplay(detailDataMap.getKey());
                        if (null == fieldBean) {
                            newDetailDataMap.put(detailDataMap.getKey(), detailDataMap.getValue());
                            continue;
                        }
                        newDetailDataMap.put(fieldBean.getName(), detailDataMap.getValue());
                    }
                    newDtlDataList.add(newDetailDataMap);
                }
                if (newDtlDataList.isEmpty()) {
                    continue;
                }
                subParams.put(tableBean.getTableName(), newDtlDataList);
            }else {
                FormFieldBean fieldBean = formBean.getMasterTableBean().getFieldBeanByDisplay(entry.getKey());
                if (null == fieldBean) {
                    masterParams.put(entry.getKey(), entry.getValue());
                    continue;
                }
                masterParams.put(fieldBean.getName(), entry.getValue());
            }
        }
        if (MapUtil.isNotEmpty(masterParams)){
            masterId = MapUtil.getLong(formParams, "id");
            if (null != masterId){
                masterParams.put("id", masterId);
                List<FormDataMasterBean> masterDataList = cap4FormDataManager.selectMasterDataById(new Long[]{masterId}, formBean, null);
                if (CollectionUtil.isEmpty(masterDataList)) {
                    doInsert(formBean.getMasterTableBean(), fillNull, masterParams);
                }else {
                    doUpdate(formBean.getMasterTableBean(), fillNull, masterParams, masterId);
                }
            }else {
                masterId = UUIDLong.longUUID();
                masterParams.put("id", masterId);
                doInsert(formBean.getMasterTableBean(), fillNull, masterParams);
            }
        }else {
            throw new BusinessException("请传入主表ID");
        }
        if (MapUtil.isEmpty(subParams)){
            return; 
        }
        for (Map.Entry<String, Object> subEntry : subParams.entrySet()) {
            FormTableBean tableBean = formBean.getSubTableBean().stream()
                    .filter(item -> item.getTableName().equals(subEntry.getKey()))
                    .findFirst()
                    .orElse(null);
            if (null == tableBean) {
                continue;
            }
            List<Map<String,Object>> dtlDataList = (List<Map<String, Object>>) subEntry.getValue();
            if (CollectionUtil.isEmpty(dtlDataList)) {
                continue;
            }
            for (Map<String, Object> dtlData : dtlDataList) {
                dtlData.put(Enums.SubTableField.formmain_id.getKey(), UUIDLong.longUUID());
                Long dtlId = MapUtil.getLong(dtlData, "id");
                if (null == dtlId) {
                    dtlData.put(Enums.SubTableField.id.getKey(), UUIDLong.longUUID());
                    dtlData.put(Enums.SubTableField.formmain_id.getKey(), masterId);
                    doInsert(tableBean, fillNull, dtlData);
                }else {
                    List<FormDataMasterBean> exists = cap4FormDataManager.selectMasterDataById(new Long[]{dtlId}, formBean, null);
                    if (CollectionUtil.isEmpty(exists)) {
                        dtlData.put(Enums.SubTableField.id.getKey(), UUIDLong.longUUID());
                        dtlData.put(Enums.SubTableField.formmain_id.getKey(), masterId);
                        doInsert(tableBean, fillNull, dtlData);
                    }else {
                        doUpdate(tableBean, fillNull, dtlData, masterId);
                    }
                }
            }
        }
            
    }

    private static void doUpdate(FormTableBean tableBean, boolean fillNull, Map<String, Object> data, Long masterId) throws BusinessException {
        List<String> columns = new ArrayList<>();
        List<Object> params = new ArrayList<>();
        for (Map.Entry<String, Object> masterEntry : data.entrySet()) {
            String key = masterEntry.getKey();
            Object value = masterEntry.getValue();
            if (!fillNull && value == null) {
                continue;
            }
            columns.add(String.format("%s = ?", key));
            params.add(value);
        }
        if (CollectionUtil.isNotEmpty(columns)) {
            String columnsJoin = String.join(",", columns);
            String updateSql = String.format("update %s set %s where id = ?", tableBean.getTableName(), columnsJoin);
            params.add(masterId);
            try (JDBCAgent jdbcAgent = new JDBCAgent()){
                jdbcAgent.batch1Prepare(updateSql);
                jdbcAgent.batch2Add(params);
                jdbcAgent.batch3Execute();
            }catch (Exception e) {
                throw new BusinessException("执行update sql异常", e);
            }
        }
    }

    private static void doInsert(FormTableBean tableBean, boolean fillNull, Map<String, Object> data) throws BusinessException {
        List<String> columns = new ArrayList<>();
        List<Object> params = new ArrayList<>();
        List<String> sqlValues = new ArrayList<>();
        for (Map.Entry<String, Object> masterEntry : data.entrySet()) {
            String key = masterEntry.getKey();
            Object value = masterEntry.getValue();
            if (!fillNull && value == null) {
                continue;
            }
            columns.add(key);
            params.add(value);
            sqlValues.add("?");
        }
        if (CollectionUtil.isNotEmpty(columns)) {
            String columnsJoin = String.join(",", columns);
            String sqlValuesJoin = String.join(",", sqlValues);
            String insertSql = String.format("insert into %s(%s) values(%s)", tableBean.getTableName(), columnsJoin, sqlValuesJoin);
            try (JDBCAgent jdbcAgent = new JDBCAgent()){
                jdbcAgent.batch1Prepare(insertSql);
                jdbcAgent.batch2Add(params);
                jdbcAgent.batch3Execute();
            }catch (Exception e) {
                throw new BusinessException("执行insert sql异常", e);
            }
        }
    }


    private static Map.Entry<String, List<Object>> doPrepareNameQuery(String namedSql, Map<String, Object> nameParams) {
        Map<String, Object> temp = new HashMap(nameParams);

        for(Map.Entry<String, Object> entry : temp.entrySet()) {
            String key = entry.getKey();
            Object val = entry.getValue();
            if (val == null) {
            }

            if (val != null && val.getClass().isArray()) {
                List newVal = new ArrayList();
                temp.put(key, newVal);
            }
        }

        MapSqlParameterSource paramSource = new MapSqlParameterSource(temp);
        ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(namedSql);
        String sqlToUse = NamedParameterUtils.substituteNamedParameters(parsedSql, paramSource);
        Object[] objs = NamedParameterUtils.buildValueArray(parsedSql, paramSource, (List)null);
        List<Object> params = new ArrayList();

        for(Object o : objs) {
            if (o instanceof Collection) {
                params.addAll((Collection)o);
            } else {
                params.add(o);
            }
        }

        return Pair.of(sqlToUse, params);
    }
}
